import time

import redis
import datetime
import pymysql
from dbutils.pooled_db import PooledDB


# redis
def getReidsConnection():
    redis_info = {
        "host": "172.20.151.90",
        "password": "Coocaa2022",
        "port": 6379,
        "db": 8
    }

    r = redis.Redis(**redis_info, decode_responses=True)
    return r

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数

    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

def main1():
	#LOG_DIVICE_ACTIVE_V2_* 为服务当前总订阅量  LOG_DIVICE_NEW_ACTIVE_V2_* 为服务当天订阅量
    key1 = "LOG_DIVICE_ACTIVE_V2"
    key2 = "LOG_DIVICE_NEW_ACTIVE_V2"
    serId = "_ZJKJ"
    date = "_20220630"
    selectKey(key2+serId+date)
    selectKey(key1+serId+date)

def renameKeyOne(key,newkey):
    r = getReidsConnection()
    print(f'旧key:{key}')
    print(f'新key:{newkey}')
    r.rename(key, newkey)

def sismember(key,value):
    r = getReidsConnection()
    flag = r.sismember(key,value)
    print(f"{key}中是否包含{value},结果为{flag}")

def addKey(key,aId):
    r = getReidsConnection()
    r.sadd(key,aId)


def deleteKey(key):
    r = getReidsConnection()
    r.delete(key)

def selectKey(key):
    r = getReidsConnection()
    num = r.scard(key)
    print(f"{key}的长度为{num}")
    return num

def getValueByKey(key):
    r = getReidsConnection()
    value = r.get(key)
    print(f"{key}的值为{value}")


def listKey(keyTemp):
    r = getReidsConnection()
    keys = r.keys()
    for key in keys:
        if key.__contains__(f'{keyTemp}'):
           print(f"{key}数量为:{r.scard(key)}")

def ttlKey(key):
    r = getReidsConnection()
    # 获取 key 的过期时间
    expire_time = r.pttl(key)

    # 将过期时间转换为可读的格式
    if expire_time == -2:
        print('Key does not exist')
    elif expire_time == -1:
        print('Key exists but has no expire time')
    else:
        expire_time = time.strftime('%H:%M:%S', time.gmtime(expire_time / 1000))
        print('Key will expire at', expire_time)

#获取截止7天前曾经订阅过的设备
#serId 为服务 day 天数
def selectAllKey(serId,day):
    r = getReidsConnection()
    keys = r.keys()
    #print (type(keys))
    newKey = f'LOG_DIVICE_ACTIVE_V2_ONCE{day}_{serId}'
    date7 = getdate(day)
    print(date7)
    for key in keys:
        if key.__contains__(f'LOG_DIVICE_NEW_ACTIVE_V2_{serId}'):
            day = key[-8:]
            if(int(day)<=int(date7)):
                r.sunionstore(newKey,key,newKey)
                print(f"{key}数量为:{r.scard(key)}")
                #print(r.sunion(key,newKey))
                print(f"取交集后:{newKey}数量为:{r.scard(newKey)}")

#获取截止目前为止累计订阅数
#serId 为服务
def selectAllOnceKey(serId):
    r = getReidsConnection()
    keys = r.keys()
    #print (type(keys))
    newKey = f'LOG_DIVICE_ACTIVE_V2_ONCE_{serId}'
    for key in keys:
        if key.__contains__(f'LOG_DIVICE_ACTIVE_V2_{serId}'):
            r.sunionstore(newKey,key,newKey)
            print(f"{key}数量为:{r.scard(key)}")
            #print(r.sunion(key,newKey))
            print(f"取交集后:{newKey}数量为:{r.scard(newKey)}")

#获取 从新增 后7天 仍然订阅的设备
#serId 服务 day 天数
def selectAllKey_Retention(serId,day):
    r = getReidsConnection()
    keys = r.keys()
    #print (type(keys))
    newKey = f'LOG_DIVICE_ACTIVE_V2_RETENTION{day}_{serId}'
    newTempKey = f'LOG_DIVICE_ACTIVE_V2_TEMP{day}_{serId}'
    for key in keys:
        if key.__contains__(f'LOG_DIVICE_NEW_ACTIVE_V2_{serId}'):
            print(f"原来的：{key},数量为:{r.scard(key)}")
            date7 = strToTime(key[-8:],day)
            date7key = f"LOG_DIVICE_ACTIVE_V2_{serId}_{date7}"
            print(f"转换后：{date7key},数量为:{r.scard(date7key)}")
            # N日且N+7日服务处于打开的设备
            r.sinterstore(newTempKey,key,date7key)
            r.sunionstore(newKey, newTempKey, newKey)
            print(f"取交集后:{newKey}数量为:{r.scard(newKey)}")

def renameKey(serId):
    r = getReidsConnection()
    keys = r.keys()
    # print (type(keys))
    redisStr = getRedisStr(serId)
    for key in keys:
        if key.__contains__(f'LOG_DIVICE_ACTIVE_V2_ONCE7_{serId}'):
            #newKey = f'LOG_DEVICE_ACTIVE_{serId}_{key[-8:]}'
            newKey = f'LOG_DEVICE_ACTIVE_ONCE7_{serId}'
            print(f'旧key:{key}')
            print(f'新key:{newKey}')
            r.rename(key,newKey)
        if key.__contains__(f'LOG_DIVICE_NEW_ACTIVE_{redisStr}_{serId}'):
            newKey = f'LOG_DEVICE_NEW_ACTIVE_{serId}_{key[-8:]}'
            #newKey = f'LOG_DEVICE_ACTIVE_RETENTION7_{serId}'
            print(f'旧key:{key}')
            print(f'新key:{newKey}')
            #r.rename(key,newKey)

def getRedisStr(serId):
    #v1 表示执行一次 v2表示未执行一次
    if serId =='ZJKJ':
        redisStr = "V2"
    elif serId =='JKZX':
        redisStr = "V1"
    elif serId =='YJYL':
        redisStr = "V1"
    elif serId =='ZJTX':
        redisStr = "V1"
    elif serId =='SJTX':
        redisStr = "V2"
    else:
        redisStr = "V1"
    return redisStr

def printKeys(serId):
    r = getReidsConnection()
    keys = r.keys()
    # print (type(keys))
    for key in keys:
        if key.__contains__(f'LOG_DIVICE_ACTIVE_V2_ONCE_{serId}'):
            print(f'key:{key}')

def test01():
    r = getReidsConnection()
    redisStr = 'V2'
    serId = 'zjkj'
    todoKey = f"LOG_DIVICE_ACTIVE_{redisStr}_{serId.upper()}_{toDay}"
    beforeKey = f"LOG_DIVICE_ACTIVE_{redisStr}_{serId.upper()}_{beforeDay}"
    newKey = f"LOG_DIVICE_NEW_ACTIVE_{redisStr}_{serId.upper()}_{toDay}"
    # 取 在当日订阅设备 但不在昨日的订阅设备中的 设备 作为当日的新增设备 存入redis
    r.sdiffstore(newKey, beforeKey, todoKey)
    todoNun = r.scard(todoKey)
    beforeNun = r.scard(beforeKey)
    newNun = r.scard(newKey)
    logDetail = f'{todoKey}日累计订阅设备{todoNun},昨日累计订阅设备{beforeNun},今日流失订阅设备{newNun}'
    print(logDetail)

def saveZmgnActive(date):
    try:

        # 获取当日助眠功能订阅设备
        conn, cur = getDataConnection()
        r = getReidsConnection()

        #获取当日助眠功能执行的设备
        selectExecuteSql = f"select t.active_id from log_common_service_{yyMMddDate} t where t.`key` = 'saEnterPage'";
        cur.execute(selectExecuteSql)
        resultNum = cur.fetchall()
        key = f"LOG_DEVICE_ACTIVE_ZMGN_EXECUTE_" + date
        for result in resultNum:
            r.sadd(key, result[0])
        # 设置有效期
        r.expire(key, expire_time)
        print(f'助眠功能:当前服务执行次数为{len(resultNum)}')
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def saveZmgnNew7dayAvtive(toDay):
    r = getReidsConnection()
    new7DayKey = f"LOG_DEVICE_NEW_ACTIVE_7DAY_ZMGN"
    r.delete(new7DayKey)
    for i in range(1, 8):
        beforeDay = strToTime(toDay, -i)
        dayNewKey = f"LOG_DEVICE_NEW_ACTIVE_ZMGN_{beforeDay}"
        print(dayNewKey)
        print(r.scard(dayNewKey))
        r.sunionstore(new7DayKey, dayNewKey, new7DayKey)
    new7DayKeyNum = r.scard(new7DayKey)
    logDetail = f'助眠功能:最近新增的设备数为{new7DayKeyNum}'
    print(logDetail)


def saveRedis(toDay):
    r = getReidsConnection()
    #前7天日期
    before7dKey = f"LOG_DEVICE_ACTIVE_ZMGN_{toDay}"
    day7KeyNum = r.scard(before7dKey)
    onceKey = f'LOG_DEVICE_ACTIVE_ONCE7_ZMGN'
    onceKeyNum = r.scard(onceKey)
    #将7天前的订阅激活id 加入到 "截止7天前曾经订阅过的设备"中
    r.sunionstore(onceKey, before7dKey, onceKey)
    newOnceNum = r.scard(onceKey)
    print(f'助眠功能累计订阅量为{onceKeyNum}')

def saveZmgnOnceAndRetentionActive(toDay):
    r = getReidsConnection()
    #前7天日期
    before7dKey = f"LOG_DEVICE_ACTIVE_ZMGN_{toDay}"
    day7KeyNum = r.scard(before7dKey)
    onceKey = f'LOG_DEVICE_ACTIVE_ONCE7_ZMGN'
    onceKeyNum = r.scard(onceKey)
    #将7天前的订阅激活id 加入到 "截止7天前曾经订阅过的设备"中
    r.sunionstore(onceKey, before7dKey, onceKey)
    newOnceNum = r.scard(onceKey)
    logDetail = f'助眠功能:当天助眠功能订阅量{day7KeyNum},旧累计订阅量{onceKeyNum}新的累计订阅数为{newOnceNum}'
    print(logDetail)

#将指定字符串 dateStr ,偏移 offsetInt 天后再输出字符串
#如 20220805 偏移7天 即增加7天后 输出 20220812
def strToTime(dateStr,offsetInt):
    t = datetime.datetime.strptime(dateStr, "%Y%m%d")
    #print(t)
    offset = datetime.timedelta(days=offsetInt)
    re_date = (t+offset).strftime('%Y%m%d')
    #print(re_date)
    return  re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    return re_date

def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

if __name__ == '__main__':
    d =1
    toDay = getdate(d)
    beforeDay = getdate(d+1)
    yyMMddDate = getdateyyMMdd(d)
    expire_time = 60 * 60 * 24 * 8
    #saveZmgnOnceAndRetentionActive(toDay)
    #saveZmgnActive(toDay)
    #saveZmgnNew7dayAvtive(toDay)
    #saveRedis(toDay)
    #test01()
    #LOG_DIVICE_ACTIVE_V2_* 为服务当前总订阅量  LOG_DIVICE_NEW_ACTIVE_V2_* 为服务当天订阅量
    #selectKey(key1+serId+date)
    #renameKey('ALL')
    #selectAllKey('ALL',7)
    #selectAllKey_Retention('ALL',7)
    #addKey("LOG_DIVICE_ACTIVE_V2_ZJKJ_20220726","38697573")
    #selectKey("CORPUS_CONTENT_ALERT_51")
    #deleteKey("LOG_DIVICE_ACTIVE_V2_ONCE_ZJKJ")
    #printKeys("")
    #renameKey("ZJKJ")
    #renameKey("JKZX")
    #renameKey("YJYL")
    #renameKey("ZJTX")
    #renameKeyOne("LOG_DIVICE_ACTIVE_V2_ONCE_ZJKJ", "LOG_DEVICE_ACTIVE_ONCE_ZJKJ")
    #getValueByKey("CORPUS_CONTENT_ALERT_51")
    #addKey('LOG_DEVICE_ACTIVE_RETENTION7_YJYL','22222')
    #listKey('LOG_DEVICE_ACTIVE_YJYL')
    ttlKey('CONF_QUESTIONNAIRE_SWITCH')

